Programming Microsoft Visual Basic .NET/Programming Microsoft Visual Basic .NET (Microsoft Press)(X08-78517)(2002).bin/21 adonet disconnected/adonetdisconnected/dbindependentcode.vb

Text File | 2002-03-16 | 6KB | 143 lines

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data.Common

' this module contains examples of how you can write
' database independent code - to see it in action you must
' make Main the startup routine for this project

Module DBIndependentCode
    Sub Main()
        ExampleCode()
    End Sub

    Sub ExampleCode()
        ' Start with the connection string.
        Dim connStr As String = BiblioConnString
        ' Uncomment next line to check that it works also with the SQL provider.
        connStr = SqlPubsConnString

        ' Create a connection object, assign to a generic IDbConnection variable.
        Dim cn As IDbConnection = CreateConnection(connStr)
        cn.Open()
        ' Create a command on this connection.
        Dim cmd As IDbCommand = cn.CreateCommand
        ' In this case the CommandText must be assigned separatedly.
        cmd.CommandText = "SELECT * FROM Publishers"
        ' Get a DataReader object.
        Dim dr As IDataReader = cmd.ExecuteReader

        ' Write field values and close the DataReader.
        Do While dr.Read And False
            Dim i As Integer
            For i = 0 To dr.FieldCount - 1
                Debug.Write(dr(i))
            Next
            Debug.WriteLine("")
        Loop
        dr.Close()

        ' Open a transaction.
        Dim tr As IDbTransaction = cn.BeginTransaction
        Try
            ' perform your database task here.
            ' ...
        Catch ex As Exception
            ' Rollback everything in case of error.
            tr.Rollback()
            tr = Nothing
        Finally
            ' if the transaction is still active, commit it.
            If Not (tr Is Nothing) Then tr.Commit()
        End Try

        ' Create a DataAdapter in a database-agnostic way
        Dim da As DbDataAdapter = CreateDataAdapter("SELECT * FROM Titles", cn)
        Dim ds As New DataSet()
        da.Fill(ds, "Titles")

        ' Change a field (without changing anything)
        ds.Tables("Titles").Rows(0)(1) = ds.Tables("Titles").Rows(0)(1).ToString & "*"

        ' Dynamically add an event to the DataAdapter.
        AddHandlerByName(da, "RowUpdated", New OleDbRowUpdatedEventHandler(AddressOf OnOleDbRowUpdated), New SqlRowUpdatedEventHandler(AddressOf OnSqlRowUpdated))
        ' Update the data source.
        da.Update(ds, "Titles")

        cn.Close()
    End Sub

    ' Create a suitable connection object for a given connection string.
    Function CreateConnection(ByVal connString As String) As IDbConnection
        If connString.ToLower.IndexOf("provider=") >= 0 Then
            Return New OleDbConnection(connString)
        Else
            Return New SqlConnection(connString)
        End If
    End Function

    ' Create a suitable DataAdapter object for a given connection object.
    Function CreateDataAdapter(ByVal sql As String, ByVal cn As IDbConnection) As DbDataAdapter
        If TypeOf cn Is OleDbConnection Then
            ' Create an OleDbDataAdapter and initialize its properties.
            Dim da As New OleDbDataAdapter(sql, DirectCast(cn, OleDbConnection))
            Dim cb As New OleDbCommandBuilder(da)
            da.UpdateCommand = cb.GetUpdateCommand
            da.DeleteCommand = cb.GetDeleteCommand
            da.InsertCommand = cb.GetInsertCommand
            Return da

        ElseIf TypeOf cn Is SqlConnection Then
            ' Create an SqlDataAdapter and initialize its properties.
            Dim da As New SqlDataAdapter(sql, DirectCast(cn, SqlConnection))
            Dim cb As New SqlCommandBuilder(da)
            da.UpdateCommand = cb.GetUpdateCommand
            da.DeleteCommand = cb.GetDeleteCommand
            da.InsertCommand = cb.GetInsertCommand
            Return da
        Else
            Throw New ArgumentException()
        End If
    End Function

    ' A generic routine that takes an object, an event name, and a list of potential
    ' delegates to event handlers, and selects the first delegate that matches the
    ' expected signature of the event handler.
    Sub AddHandlerByName(ByVal obj As Object, ByVal eventName As String, ByVal ParamArray events() As [Delegate])
        ' Get the type of the object argument.
        Dim ty As System.Type = obj.GetType
        ' Get the EventInfo corresponding to the requested event
        Dim evInfo As System.Reflection.EventInfo = ty.GetEvent(eventName)
        ' Get the delegate class that represents the event procedure
        Dim evType As System.Type = evInfo.EventHandlerType

        ' Compare this type with arguments being passed.
        Dim del As [Delegate]
        For Each del In events
            If del.GetType Is evType Then
                ' If this is the right delegate, add it to the list of event handlers and exit.
                evInfo.AddEventHandler(obj, del)
                Exit Sub
            End If
        Next

    End Sub

    ' An example of two event procedure that delegate their job to a common routine.
    Sub OnOleDbRowUpdated(ByVal sender As Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs)
        Debug.WriteLine("OnOleDbRowUpdated")
        OnRowUpdated(sender, e)
    End Sub

    Sub OnSqlRowUpdated(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)
        Debug.WriteLine("SqlDbRowUpdated")
        OnRowUpdated(sender, e)
    End Sub

    Sub OnRowUpdated(ByVal sender As Object, ByVal e As System.Data.Common.RowUpdatedEventArgs)
        ' ...
    End Sub

End Module